{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5c0e8855",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Sheet1']\n"
     ]
    }
   ],
   "source": [
    "## openpyxl读写Excel文件\n",
    "\n",
    "import os,sys\n",
    "import openpyxl\n",
    "\n",
    "### 1. 读取Excel文件\n",
    "excel_file = \"../4-测试数据/优衣库销售数据.xlsx\"\n",
    "\n",
    "workbook = openpyxl.load_workbook(excel_file)\n",
    "sheet_names = workbook.sheetnames\n",
    "print(sheet_names)\n",
    "sheet = workbook.active"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2fcf8c60",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2048 15\n",
      "商店ID\n",
      "831\n",
      "('商店ID', '门店所在城市', '渠道', '性别群体', '年龄群体', '产品类别', '客户数量', '销售金额', '订单数量', '购买的产品数量', '成本', '单价', '利润', '订单日期', '星期')\n"
     ]
    }
   ],
   "source": [
    "\n",
    "print(sheet.max_row,sheet.max_column)\n",
    "print(sheet['A1'].value)\n",
    "print(sheet['A2'].value)\n",
    "for row in sheet.iter_rows(values_only=True):\n",
    "    print(row)\n",
    "    break # 打印一行终止"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e8838799",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用后需要关闭文件\n",
    "workbook.close()  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34ad4723",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用Pandas读取Excel文件\n",
    "import pandas as pd\n",
    "excel_file = '../4-测试数据/优衣库销售数据.xlsx'\n",
    "# 读取Excel文件\n",
    "df = pd.read_excel(excel_file)\n",
    "\n",
    "# 打印前5行数据\n",
    "print(df.head(1))\n",
    "print(\"--------------------------------\")\n",
    "# 打印数据类型\n",
    "print(\"df.dtypes:\",df.dtypes)\n",
    "print(\"--------------------------------\")\n",
    "print(\"df.info():\",df.info())\n",
    "print(\"--------------------------------\")\n",
    "print(\"df.describe():\",df.describe())\n",
    "print(\"--------------------------------\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65a6498a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "读取数据行数： 2042\n",
      "  store_id store_city sales_channel gender age_range product_category  \\\n",
      "0      831         杭州            线下      女     25-29               T恤   \n",
      "\n",
      "  sales_amt sales_qty unit_cost unit_price profits            sales_day  \n",
      "0     199.0         1      49.0      199.0   150.0  2023-01-13 00:00:00  \n"
     ]
    }
   ],
   "source": [
    "# 读取Excel方法一： 精确读取指定列\n",
    "sheet_name = \"Sheet1\" # 工作表名称\n",
    "skiprows = 5 # 跳过前5行\n",
    "header = 0 # 是否使用表头，0表示使用，None表示不使用\n",
    "# 列名\n",
    "names = ['store_id','store_city','sales_channel','gender','age_range','product_category',\n",
    "         'sales_amt','sales_qty','unit_cost','unit_price','profits','sales_day'] \n",
    "usecols = \"A:F,H,J:N\" # 使用的列 **注意：列名和列索引不能同时使用**\n",
    "converters = {'store_id': str, 'store_city': str, 'sales_channel': str,'gender': str, 'age_range': str, 'product_category': str,\n",
    "              'sales_amt': float,'sales_qty': int,  'unit_cost': float, 'unit_price': float, 'profits': float,\n",
    "              'sales_day': str} # 指定列的数据类型\n",
    "\n",
    "\n",
    "df2 = pd.read_excel(\n",
    "    excel_file,\n",
    "    sheet_name=sheet_name,\n",
    "    header=header,\n",
    "    skiprows=skiprows,\n",
    "    names=names,\n",
    "    usecols=usecols,\n",
    "    converters=converters,\n",
    "    engine='openpyxl'\n",
    ")\n",
    "print('读取数据行数：',df2.shape[0])\n",
    "print(df2.head(1))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "cf668a08",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "读取数据第一行： 2047\n",
      "  商店ID 门店所在城市  渠道 性别群体   年龄群体 产品类别 客户数量 销售金额 订单数量 购买的产品数量  成本  单价  利润  \\\n",
      "0  831     杭州  线下    女  20-24   T恤    1   59    1       1  49  59  10   \n",
      "\n",
      "                  订单日期   星期  \n",
      "0  2023-01-02 00:00:00  星期一  \n",
      "处理后第一行：\n",
      "  商店ID 门店所在城市  渠道 性别群体   年龄群体 产品类别  客户数量  销售金额  订单数量  购买的产品数量    成本    单价  \\\n",
      "0  831     杭州  线下    女  20-24   T恤     1  59.0     1        1  49.0  59.0   \n",
      "\n",
      "     利润                 订单日期   星期  \n",
      "0  10.0  2023-01-02 00:00:00  星期一  \n"
     ]
    }
   ],
   "source": [
    "# 读取Excel方法二：先读取后处理\n",
    "# 读取第一个sheet页，全部已字符串读取\n",
    "df3 = pd.read_excel(excel_file,dtype=str)\n",
    "#数据清洗-填充NA值\n",
    "df3.fillna(0,inplace=True)\n",
    "# 打印前1行数据\n",
    "print('读取数据第一行：',len(df3))\n",
    "print(df3.head(1))\n",
    "df3.dropna(inplace=True)\n",
    "# 将客户数量\t销售金额\t订单数量\t购买的产品数量\t成本\t单价\t利润 字段设置为int或者float\n",
    "df3['客户数量'] = df3['客户数量'].astype(int)\n",
    "df3['销售金额'] = df3['销售金额'].astype(float)\n",
    "df3['订单数量'] = df3['订单数量'].astype(int)\n",
    "df3['购买的产品数量'] = df3['购买的产品数量'].astype(int)\n",
    "df3['成本'] = df3['成本'].astype(float)\n",
    "df3['单价'] = df3['单价'].astype(float)\n",
    "df3['利润'] = df3['利润'].astype(float)\n",
    "# 打印前1行数据\n",
    "print('处理后第一行：')\n",
    "print(df3.head(1))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "96ff9cdd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 销售金额  购买的产品数量\n",
      "门店所在城市 产品类别                   \n",
      "0      0         0.00        0\n",
      "杭州     T恤    27959.86      344\n",
      "       当季新品   8990.21       76\n",
      "       毛衣     5659.37       36\n",
      "       牛仔裤    3369.50       33\n",
      "       短裤     1326.00       32\n",
      "       袜子     2555.00       74\n",
      "       裙子     2060.26       17\n",
      "       运动     1872.04       29\n",
      "       配件     7459.98       82\n",
      "深圳     T恤    66297.47      789\n",
      "       当季新品  29125.55      259\n",
      "       毛衣    13990.28       82\n",
      "       牛仔裤   11919.52      126\n",
      "       短裤     5132.64      137\n",
      "       袜子     7114.82      192\n",
      "       裙子     5096.32       40\n",
      "       运动     7604.65      145\n",
      "       配件    27279.29      331\n",
      "西安     T恤    16447.50      184\n",
      "       当季新品   3922.10       40\n",
      "       毛衣     2165.00       13\n",
      "       牛仔裤    1041.00        9\n",
      "       短裤      780.00       20\n",
      "       袜子      930.00       26\n",
      "       裙子      974.00        6\n",
      "       运动      348.27        8\n",
      "       配件     3818.67       37\n",
      "重庆     T恤    23892.02      294\n",
      "       当季新品   5862.70       62\n",
      "       毛衣     6813.00       37\n",
      "       牛仔裤    3949.00       47\n",
      "       短裤      903.00       25\n",
      "       袜子     2175.11       60\n",
      "       裙子     1825.00       11\n",
      "       运动     3693.66       53\n",
      "       配件     8662.24      102\n"
     ]
    }
   ],
   "source": [
    "from matplotlib import pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "df3.dropna(inplace=True)\n",
    "grouped = df3.groupby(['门店所在城市', '产品类别'])\n",
    "groupd_sum = grouped.agg({'销售金额': 'sum', '购买的产品数量': 'sum'})\n",
    "\n",
    "print(groupd_sum.head(100))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
